package com.example.demo1.dao;

import com.example.demo1.entity.User;
import com.example.demo1.util.JDBCUtil;
import com.example.demo1.util.StringUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 预编译语句（PreparedStatement） 防止 SQL 注入
 * try-with-resources 会自动关闭 connection
 * SQL 插入语句，? 是占位符，对应后面的参数
 * 创建预编译语句对象，SQL 已发送到数据库预编译
 * 设置占位符参数（从 1 开始索引）
 * 执行更新操作，返回受影响的行数（插入成功返回 1）
 * PreparedStatement 未在 try() 中声明，但会随 connection 关闭而自动关闭
 * 获取连接 → 2. 创建预编译 SQL → 3. 设置参数 → 4. 执行更新 → 5. 自动关闭资源。
 * */

/**
 * 这个类仅出现sql语句，不要出现输入输出流
 * 这里会将这些信息通过对象进行传递
 * */
public class UserDaoImpl implements UserDao {


    @Override
    public int addUser(User user) {
        try(Connection connection = JDBCUtil.getConnection()){
            String sql = "insert into user(user_id,user_name,pswd,user_nickname,user_identity,user_sex,user_picture,user_phnum,user_email,user_lotime) values(?,?,?,?,?,?,?,?,?,now())";
            PreparedStatement preSta = connection.prepareStatement(sql);
            preSta.setString(1, user.getUser_id());
            preSta.setString(2,user.getUser_name());
            preSta.setString(3,user.getPswd());
            preSta.setString(4,user.getUser_nickname());
            preSta.setString(5,user.getUser_identity());
            preSta.setString(6,user.getUser_sex());
            preSta.setString(7,user.getUser_picture());
            preSta.setString(8,user.getUser_phnum());
            preSta.setString(9,user.getUser_email());
            //preSta.setString(10,user.getUser_lotime());
            int num = preSta.executeUpdate();
            return num;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int deleteUser(Serializable id) {
        try(Connection conn = JDBCUtil.getConnection()){
            String sql = "delete from user where user_id = ?";
            PreparedStatement preSta = conn.prepareStatement(sql);
            preSta.setObject(1,id);
            int num = preSta.executeUpdate();
            return num;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int updateUser(User user) {
        if (user == null || user.getUser_id() == null) {
            throw new IllegalArgumentException("更新用户时 user_id 不能为空");
        }
        try(Connection conn = JDBCUtil.getConnection()){
            ArrayList<Object> list = new ArrayList<>();
            ArrayList<String> fieldList = new ArrayList<>();

            // 收集所有待更新的字段
            if (StringUtil.isNotEmpty(user.getPswd())) {
                fieldList.add("pswd = ?");
                list.add(user.getPswd());
            }

            if (StringUtil.isNotEmpty(user.getUser_nickname())) {
                fieldList.add("user_nickname = ?");
                list.add(user.getUser_nickname());
            }

            if (StringUtil.isNotEmpty(user.getUser_identity())) {
                fieldList.add("user_identity = ?");
                list.add(user.getUser_identity());
            }

            if (StringUtil.isNotEmpty(user.getUser_sex())) {
                fieldList.add("user_sex = ?");
                list.add(user.getUser_sex());
            }

            if (StringUtil.isNotEmpty(user.getUser_picture())) {
                fieldList.add("user_picture = ?");
                list.add(user.getUser_picture());
            }

            if (StringUtil.isNotEmpty(user.getUser_phnum())) {
                fieldList.add("user_phnum = ?");
                list.add(user.getUser_phnum());
            }

            if (StringUtil.isNotEmpty(user.getUser_email())) {
                fieldList.add("user_email = ?");
                list.add(user.getUser_email());
            }

            // 如果没有需要更新的字段，直接返回
            if (fieldList.isEmpty()) {
                System.out.println("无更新字段");
                return 0;
            }

            // 使用逗号和空格连接所有字段，确保无多余逗号
            String setClause = String.join(", ", fieldList);
            StringBuffer sql = new StringBuffer("UPDATE user SET ");
            sql.append(setClause);
            sql.append(" WHERE user_id = ?");
            list.add(user.getUser_id()); // 添加 WHERE 子句的参数

            PreparedStatement preSta = conn.prepareStatement(sql.toString());
            for(int i = 0;i<list.size();i++){
                preSta.setObject(i+1,list.get(i));
            }
            return preSta.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public User selectUser(Serializable id) {
        try(Connection conn = JDBCUtil.getConnection()){
            String sql ="select * from user where user_id = ?";
            PreparedStatement preSta = conn.prepareStatement(sql);
            preSta.setObject(1,id);
            ResultSet rs =  preSta.executeQuery();
            if(rs.next()){
                String user_id = rs.getString("user_id");
                String user_name = rs.getString("user_name");
                String pswd = rs.getString("pswd");
                String user_nickname = rs.getString("user_nickname");
                String user_identity = rs.getString("user_identity");
                String user_sex = rs.getString("user_sex");
                String user_picture = rs.getString("user_picture");
                String user_phnum = rs.getString("user_phnum");
                String user_email = rs.getString("user_email");
                String user_lotime = rs.getString("user_lotime");

                User user = new User(user_id,user_name,pswd,user_nickname,user_identity,user_sex,user_picture,user_phnum,user_email,user_lotime);
                return user;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return null;
    }

public User selectUserByLo(User user) {
    String sql = "SELECT * FROM user WHERE user_name=?"; // 只查用户名
    try (Connection conn = JDBCUtil.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, user.getUser_name());
        try (ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                User result = new User();
                // 填充用户对象...
                result.setPswd(rs.getString("pswd")); // 必须获取加密后的密码
                return result;
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

    @Override
    public ArrayList<User> selectUsers(User user) {
        ArrayList <User> list = new ArrayList<>();
        try(Connection coon = JDBCUtil.getConnection()){
            StringBuffer sql = new StringBuffer( "select * from user where 1=1");
            ArrayList<Object> params = new ArrayList<>();
            if(StringUtil.isNotEmpty(user.getUser_id())){
                sql.append(" and user_id = ?");
                params.add(user.getUser_id());
            }
            if(StringUtil.isNotEmpty(user.getUser_name())){
                sql.append(" and user_name like ?");
                params.add("%" + user.getUser_name() + "%");
            }

            if(StringUtil.isNotEmpty(user.getUser_identity())){
                sql.append(" and user_identity = ?");
                params.add(user.getUser_identity());
            }
            if(StringUtil.isNotEmpty(user.getUser_sex())){
                sql.append(" and user_sex = ?");
                params.add(user.getUser_sex());
            }
            if(StringUtil.isNotEmpty(user.getUser_phnum())){
                sql.append(" and user_phnum like ?");
                params.add("%" + user.getUser_phnum() + "%");
            }
            if(StringUtil.isNotEmpty(user.getUser_email())){
                sql.append(" and user_email like ?");
                params.add("%" + user.getUser_email() + "%");
            }
            if(StringUtil.isNotEmpty(user.getUser_lotime())){
                sql.append(" and user_lotime like ?");
                params.add("%" + user.getUser_lotime() + "%");
            }

            PreparedStatement preSta = coon.prepareStatement(sql.toString());
            for(int i = 0;i < params.size();i++){
                preSta.setObject(i+1,params.get(i));
            }
            //执行查询
            ResultSet rs = preSta.executeQuery();
            while(rs.next()) {
                String user_id = rs.getString("user_id");
                String user_name = rs.getString("user_name");
                String pswd = rs.getString("pswd");
                String user_nickname = rs.getString("user_nickname");
                String user_identity = rs.getString("user_identity");
                String user_sex = rs.getString("user_sex");
                String user_picture = rs.getString("user_picture");
                String user_phnum = rs.getString("user_phnum");
                String user_email = rs.getString("user_email");
                String user_lotime = rs.getString("user_lotime");

                User user1 = new User(user_id,user_name,pswd,user_nickname,user_identity,user_sex,user_picture,user_phnum,user_email,user_lotime);
                list.add(user1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }


        return list;
    }
}